

import java.sql.*;
import java.util.Scanner;

public class demo913 {

    public static void main(String[] args) {


    }
    public static void m1(){
        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String pw = "@Qq00214113";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url,user,pw);
            if (connection!=null){
                System.out.println("连接成功");
            }
            String sql="select * from user";
            PreparedStatement de=connection.prepareStatement(sql);
            ResultSet ui= de.executeQuery();
            while (ui.next()){
                System.out.println("id:"+ui.getInt("id")+",name:"+ui.getString("name")+",password:"+ui.getString("pass"));
            }
            connection.close();
            de.close();
            ui.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
    public static void m2(){
        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String pw = "123456";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url,user,pw);
            if (connection!=null){
                System.out.println("连接成功");
            }
            System.out.println("请输入你想查询的姓名关键词");
            Scanner scanner=new Scanner(System.in);
            String na= scanner.nextLine();
            String sql="select * from user where name like ?";
            PreparedStatement de=connection.prepareStatement(sql);
            de.setString(1,"%"+na+"%");
            ResultSet ui= de.executeQuery();
            while (ui.next()){
                System.out.println("id:"+ui.getInt("id")+",name:"+ui.getString("name")+",password:"+ui.getString("pass"));
            }
            connection.close();
            de.close();
            ui.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
    public static void m3(){
        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String pw = "@Qq20041214113";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url,user,pw);
            if (connection!=null){
                System.out.println("连接成功");
            }
            System.out.println("请输入你的姓名和密码");
            Scanner scanner=new Scanner(System.in);
            String name=scanner.next();
            String passward=scanner.next();
            String sql="insert into user(name,pass) values (?,?)";
            PreparedStatement de=connection.prepareStatement(sql);
            de.setString(1,name);
            de.setString(2,passward);
            de.executeUpdate();
            System.out.println("已成功添加您的账户");
            connection.close();
            de.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public static void m4(){
        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String pw = "@Qq20041214113";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url,user,pw);
            if (connection!=null){
                System.out.println("连接成功");
            }
            System.out.println("请输入您想删除的用户名");
            Scanner scanner=new Scanner(System.in);
            String name=scanner.next();
            String sql="delete from user where name=?";
            PreparedStatement de=connection.prepareStatement(sql);
            de.setString(1,name);
            de.executeUpdate();
            System.out.println("已成功删除");
            connection.close();
            de.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public static void m5(){

        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String pw = "@Qq20041214113";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url,user,pw);
            if (connection!=null){
                System.out.println("连接成功");
            }
            Scanner scanner=new Scanner(System.in);
            System.out.println("请输入您想修改的用户的用户名");
            String lname=scanner.nextLine();
            System.out.println("请输入新的用户名和密码");
            String xname=scanner.next();
            String mima=scanner.next();
            String sql="select id from user where name=?";
            PreparedStatement de=connection.prepareStatement(sql);
            de.setString(1,lname);
            ResultSet ft=de.executeQuery();
            int h= chaid(connection,lname);
            String xsql="update user set name=?,pass=? where id=?";
            PreparedStatement gh=connection.prepareStatement(xsql);
            gh.setString(1,xname);
            gh.setString(2,mima);
            gh.setInt(3,h);
            gh.executeUpdate();
            System.out.println("修改成功");
            connection.close();
            de.close();
            ft.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public static int chaid(Connection con,String name){
        String dr="Select id from user where name=?";
        try {PreparedStatement po=con.prepareStatement(dr);
            po.setString(1,name);
            ResultSet rt=po.executeQuery();
            if(rt.next()){
                return rt.getInt("id");
            }

        }catch (SQLException e){
            System.out.println("查找用户id出现错误"+e);
        }
        return -1;
    }

}
